# !usr/bin/env python
# -*- coding:utf-8 _*-
"""
@Author:张广勤
@Web site: https://www.tunan.wang
@Github:www.github.com
 
@File:curd1_0.py
@Time:2024/12/2 20:03

@Motto:不积跬步无以至千里，不积小流无以成江海！
"""

import streamlit as st
import pymysql

# 数据库连接配置
db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': '12345',
    'database': 'yanbei2023',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor,
}

# 连接到数据库
def create_connection():
    return pymysql.connect(**db_config)

# 显示所有表名
def get_table_names():
    with create_connection().cursor() as cursor:
        cursor.execute("SHOW TABLES")
        tables = cursor.fetchall()
        return [table['Tables_in_yanbei2023'] for table in tables]

# 显示表的数据
def get_table_data(table_name):
    with create_connection().cursor() as cursor:
        cursor.execute(f"SELECT * FROM `{table_name}` LIMIT 100")  # 限制返回的行数以避免加载过多数据
        return cursor.fetchall()

# 插入数据
def insert_data(table_name, data):
    columns = ', '.join(data.keys())
    placeholders = ', '.join(['%s'] * len(data))
    sql = f"INSERT INTO `{table_name}` ({columns}) VALUES ({placeholders})"
    connection = create_connection()  # 创建数据库连接
    try:
        with connection.cursor() as cursor:
            cursor.execute(sql, list(data.values()))
        connection.commit()
    finally:
        connection.close()  # 确保连接被关闭

# 更新数据
def update_data(table_name, data, condition):
    sets = ', '.join([f"`{key}` = %s" for key in data.keys()])
    sql = f"UPDATE `{table_name}` SET {sets} WHERE {condition}"
    connection = create_connection()  # 创建数据库连接
    try:
        with connection.cursor() as cursor:
            cursor.execute(sql, list(data.values()))
        connection.commit()
    finally:
        connection.close()  # 确保连接被关闭

# 删除数据
def delete_data(table_name, condition):
    sql = f"DELETE FROM `{table_name}` WHERE {condition}"
    connection = create_connection()  # 创建数据库连接
    try:
        with connection.cursor() as cursor:
            cursor.execute(sql)
        connection.commit()
    finally:
        connection.close()  # 确保连接被关闭


# Streamlit界面
def main():
    st.title("MySQL 数据库操作")

    # 显示所有表名并选择
    tables = get_table_names()
    selected_table = st.selectbox("选择一个表:", tables)

    # 显示表数据
    if selected_table:
        table_data = get_table_data(selected_table)
        st.subheader(f"{selected_table} 表数据:")
        st.write(table_data)

        # 插入数据
        if st.button("添加一行数据"):
            new_data = {}
            for column in table_data[0].keys() if table_data else []:
                new_data[column] = st.text_input(f"{column}:") if isinstance(table_data[0][column], str) else st.number_input(f"{column}:", min_value=1, value=1)
            insert_data(selected_table, new_data)
            st.success("数据插入成功!")
            table_data = get_table_data(selected_table)  # 刷新数据
            st.write(table_data)

        # 更新数据（这里简化为例：更新第一行数据）
        if st.button("更新第一行数据") and table_data:
            update_data_dict = {}
            for column in table_data[0].keys():
                update_data_dict[column] = st.text_input(f"新 {column} 值:", value=str(table_data[0][column]))
            update_data(selected_table, update_data_dict, f"`id` = {table_data[0]['id']}" if 'id' in table_data[0] else "1=1")
            st.success("数据更新成功!")
            table_data = get_table_data(selected_table)  # 刷新数据
            st.write(table_data)

        # 删除数据（这里简化为例：删除第一行数据）
        if st.button("删除第一行数据") and table_data:
            delete_data(selected_table, f"`id` = {table_data[0]['id']}" if 'id' in table_data[0] else "1=1")
            st.success("数据删除成功!")
            table_data = get_table_data(selected_table)  # 刷新数据
            st.write(table_data)

if __name__ == "__main__":
    main()
